June 1966

Speeding up your PL/SQLdevelopment

by Jeff Warner

If you're doing Oracle database or application development, then you're probably using PL/SQL. Every PL/SQL programmer is familiar with using SQL*Plus to develop PL/SQL programs. Developer/2000 offers a better way: Procedure Builder. In this article we'll explore how Procedure Builder can improve your PL/SQL development.

It has a GUI

The first feature you'll notice in Procedure Builder is the graphical user interface (GUI). Procedure Builder provides separate windows for the Object Navigator, the PL/SQL interpreter, and the program unit editor. By the way, if you think the program unit editor looks familiar, you're probably right. Oracle uses it inside the other tools in Developer/2000 (Forms, Reports, and Graphics). Using the Object Navigator, you can view the relationship between your code and the library and packages. Object Navigator also provides the references and referenced-by information, as well as giving you the ability to examine the procedures and functions that make up packages and libraries. This makes it very easy to take advantage of existing code.

The PL/SQL interpreter is just what you'd expect. You can use normal SQL and PL/SQL commands and see the results in the output window.

You create your PL/SQL in the program unit editor window. As with any text editor, you can cut, copy, and paste as well as search and replace text. You can also use the editor to import and export source files. However, the editor's best feature is the Compile button. Click the Compile button, and Procedure Builder will compile your code and display any errors in the bottom of the window. Click on the error, and your cursor will move to the offending line. This will radically improve the compile-debug-run sequence. Speaking of debugging, Procedure Builder has something that will really make life easier for you.

A PL/SQL debugger

Yes, Procedure Builder includes a real debugger for PL/SQL. Its features include breakpoints, triggers, variable inspection, variable modification, step into, step over, and step out.

Having a graphical and easy-to-use debugger can dramatically improve code development. For example, to set a breakpoint in your code, simply bring the PL/SQL interpreter window to the front and double-click on the line where a breakpoint is needed.

Once you've set a breakpoint and executed the code, the debugger will stop the program at the breakpoint. From here, you can use Object Navigator to view and change the variables in the program. To resume execution, you can use Procedure Builder to step into, step over, or step out of a routine. Procedure Builder also provides an option to simply continue execution.

A powerful feature of Procedure Builder's breakpoints is the capability of adding trigger code. Trigger code is a piece of PL/SQL that executes when the breakpoint is hit. You can use this feature to log the value of a variable to the screen or a file.

If you need a conditional breakpoint, you can use a trigger. For example, let's say you want to stop execution only if the local variable i is greater than zero. First, select the line where you want the check to occur and create a trigger by clicking the right mouse button on the line and selecting Trigger. This will open the PL/SQL Trigger window. In the Trigger Body area, enter the following command to halt execution:

if debug.geti('i') > 0 then

raise debug.break;

end if;

Now the program will stop execution only when this condition is valid. You can also use the Trigger Body of a trigger to log information to a file or a screen.

Debugging server-side programs

The version of Procedure Builder shipped with version 1.2 of Developer/2000 is V1.5.5.7.0. Unfortunately, it doesn't allow you to directly debug programs on the server (this is planned for a future version). However, with the click-and-drag partition feature of Developer/2000, you can use Object Navigator to drag the program unit from the server to the local program unit and debug it there.

Lots of help

Procedure Builder includes extensive help under Windows. In addition to the normal help available on program features, Procedure Builder provides help on the built-in packages as well as the PL/SQL commands to maintain libraries, program units, and packages. Also, version 1.2 or later of Developer/2000 provides the Cue Cards and Quick Tour.

Conclusion

As you can see, Procedure Builder is a very powerful tool for development. The debugger is first-rate and the extensive help is a great addition. One other nice feature of Procedure Builder is that Oracle will license it separately from Developer/2000 if you have users who don't need the other parts of Developer/2000. If you're doing PL/SQL programming, you should take advantage of what Procedure Builder has to offer.

[Return to Index for Exploring Oracle Developer/2000 and Designer/2000 - June 1996]

Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis

Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of

Ziff-Davis Publishing Company.